/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package org.trandio.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.naming.NamingException;
import org.trandio.model.ProgramapypVO;
import org.trandio.util.ConexionDb;


/**
 *
 * @author root
 */
public class ProgramapypDAO implements ProgramapypInterface {

   

//esta function ya se encuentra en utilidades, corregir
    @Override
    public List<ProgramapypVO> consultarProgramapyp(String codigo, String descripcion) throws SQLException, NamingException {

        ConexionDb con = new ConexionDb();
        Connection conn;
        conn = con.traerConexion();

        String condicion = "";


        if(codigo!=null){
        if(!codigo.equals("")){
            condicion = " WHERE tb_programapyp.codigo = '"+codigo+"'";
        }
        }

        if(descripcion!=null){
        if(!descripcion.equals("")){
            condicion = " WHERE tb_programapyp.descripcion like '%"+descripcion+"%'";
        }
        }


        List<ProgramapypVO> listProgramapypVO = new ArrayList<ProgramapypVO>();
        String sql = "SELECT " +
                      "tb_programapyp.id,"+
                      "tb_programapyp.codigo,"+
                      "tb_programapyp.descripcion"+
                      " FROM tb_programapyp "+condicion;

        System.out.println("Sql BUSCAR tb_programapyp: "+sql);

        PreparedStatement stmt = conn.prepareStatement(sql);
        try {
            ResultSet res = stmt.executeQuery();
            try {
                while (res.next()) {
                    ProgramapypVO ProgramapypVO = new ProgramapypVO();
                    ProgramapypVO.setId(res.getInt("id"));
                    ProgramapypVO.setCodigo(res.getString("codigo"));
                    ProgramapypVO.setDescripcion(res.getString("descripcion"));
                    listProgramapypVO.add(ProgramapypVO);
                }
            } finally {
                res.close();
            }
        } finally {
            stmt.close();
            if (!conn.isClosed())
               conn.close();
        }

        return listProgramapypVO;
    }

  

  @Override
    public ProgramapypVO getProgramapyp(int id) throws SQLException, NamingException {
        ConexionDb con = new ConexionDb();
        Connection conn;
        conn = con.traerConexion();

        String sql = "SELECT id, codigo, descripcion"+
                     " FROM tb_programapyp"+
                     " WHERE tb_programapyp.id ="+id;

        System.out.println("Sql buacar programapyp ID: "+sql);

        PreparedStatement stmt = conn.prepareStatement(sql);
        ProgramapypVO ProgramapypVO = new ProgramapypVO();
        try{
        ResultSet res = stmt.executeQuery();
        try{

        if (res.next()) {
                ProgramapypVO.setId(res.getInt("id"));
                ProgramapypVO.setCodigo(res.getString("codigo"));
                ProgramapypVO.setDescripcion(res.getString("descripcion"));
                
            }
        }finally{
         res.close();
        }
        }finally{
         stmt.close();
         if (!conn.isClosed())
               conn.close();
        }
        return ProgramapypVO;

    }

    @Override
    public void guardarProgramapypEdit(ProgramapypVO ProgramapypVO) throws SQLException, NamingException {
        ConexionDb con = new ConexionDb();
        Connection conn;
        conn = con.traerConexion();

        String sql = "UPDATE tb_programapyp"+
                     " SET codigo=?, descripcion=?"+
                     " WHERE tb_programapyp.id="+ProgramapypVO.getId();

                System.out.println("SQL del Update tb_programapyp="+sql);

		PreparedStatement stmt = conn.prepareStatement(sql);

                stmt.setString(1,ProgramapypVO.getCodigo());
                stmt.setString(2,ProgramapypVO.getDescripcion());
                

                stmt.execute();
		stmt.close();
    }


    @Override
    public int guardarProgramapyp(ProgramapypVO ProgramapypVO) throws SQLException, NamingException {
        ConexionDb con = new ConexionDb();
        Connection conn;
        conn = con.traerConexion();

        System.out.println("INSERTAR tb_programapyp");
        
        String sqlAdiciona  ="INSERT INTO tb_programapyp("+
            "codigo, descripcion) "+
            "VALUES (?,?) RETURNING id;";

        PreparedStatement stmt = conn.prepareStatement(sqlAdiciona);    

        stmt.setString(1,ProgramapypVO.getCodigo());
        stmt.setString(2,ProgramapypVO.getDescripcion());
        
       
        ResultSet rs = stmt.executeQuery();
        rs.next();
        int resp = rs.getInt(1);
        System.out.println("Insert Generar: "+resp);

        rs.close();
	stmt.close();
        if (!conn.isClosed())
               conn.close();
        return resp;
    }

    @Override
    public List<ProgramapypVO> consultarProgramapyp() throws SQLException, NamingException {
        ConexionDb con = new ConexionDb();
        Connection conn;
        conn = con.traerConexion();

        String condicion = "";


        List<ProgramapypVO> listProgramapypVO = new ArrayList<ProgramapypVO>();
        String sql = "SELECT " +
                      "tb_programapyp.id,"+
                      "tb_programapyp.codigo,"+
                      "tb_programapyp.descripcion"+
                      " FROM tb_programapyp "+condicion;

        System.out.println("Sql BUSCAR tb_programapyp: "+sql);

        PreparedStatement stmt = conn.prepareStatement(sql);
        try {
            ResultSet res = stmt.executeQuery();
            try {
                while (res.next()) {
                    ProgramapypVO ProgramapypVO = new ProgramapypVO();
                    ProgramapypVO.setId(res.getInt("id"));
                    ProgramapypVO.setCodigo(res.getString("codigo"));
                    ProgramapypVO.setDescripcion(res.getString("descripcion"));
                    listProgramapypVO.add(ProgramapypVO);
                }
            } finally {
                res.close();
            }
        } finally {
            stmt.close();
            if (!conn.isClosed())
               conn.close();
        }

        return listProgramapypVO;
    }

    @Override
    public int getCantProgramapyp(String $sWhere) throws SQLException, NamingException {
        ConexionDb con = new ConexionDb();
        Connection conn;
        conn = con.traerConexion();
        int count = 0;

        String sql = "SELECT count(*) FROM tb_programapyp";

            PreparedStatement stmt = conn.prepareStatement(sql);

            try{
            ResultSet res = stmt.executeQuery();
            try{
            if (res.next()) {
                count = res.getInt(1);
            }
            }finally{
                res.close();
            }
            }finally{
                stmt.close();
                if (!conn.isClosed())
               conn.close();
            }

        return count;
    }

    @Override
    public List<ProgramapypVO> getProgramapyp(int displayStart, int displayLength, String $sWhere, String orden) throws SQLException, NamingException {
        ConexionDb con = new ConexionDb();
        Connection conn;
        conn = con.traerConexion();

         List<ProgramapypVO> lista = new ArrayList<ProgramapypVO>();
         String condicion = "";

         if(!$sWhere.equals("")){
            //condicion = " WHERE tb_factura.numero LIKE '%"+param+"%' ";
             condicion = $sWhere;
         }



        String sql = "SELECT * "+
                     " FROM tb_programapyp "+condicion+" "+orden+" LIMIT "+displayLength+" OFFSET "+displayStart;

        System.out.println("Sql BUSCAR MANUAL POR ID: "+sql);


        PreparedStatement stmt = conn.prepareStatement(sql);


        try {
            ResultSet res = stmt.executeQuery();
            try {
                while (res.next()) {
                    ProgramapypVO objVO = new ProgramapypVO();
                    objVO.setId(res.getInt("id"));
                    objVO.setCodigo(res.getString("codigo"));
                    objVO.setDescripcion(res.getString("descripcion"));                    
                    lista.add(objVO);
                }
            } finally {
                res.close();
            }
        } finally {
            stmt.close();
           if (!conn.isClosed())
               conn.close();
        }

        return lista;
    }
    
@Override
    public List<ProgramapypVO> consultarProgramapypEdadSexo(String edad, int idsexo) throws SQLException, NamingException {
        ConexionDb con = new ConexionDb();
        Connection conn;
        conn = con.traerConexion();
        String condicion = "";
        
         int intLnEdad = edad.length();

        int intEdad = (Integer.parseInt(edad.substring(0, intLnEdad - 1)));

        char chrMedEdad = edad.charAt(intLnEdad - 1);

         condicion = "where ? between substring(p.edad_inicio from 1 for char_length(p.edad_inicio)-1)::integer"
                    + " AND Substring(p.edad_final from 1 for char_length(p.edad_final)-1)::integer"
                    + " AND substring(p.edad_inicio from length(p.edad_inicio) for 2 ) = ? and p.sexo IN(3,?)";
        
        List<ProgramapypVO> listProgramapypVO = new ArrayList<ProgramapypVO>();
        String sql = "SELECT " +
                      "p.id,"+
                      "p.codigo,"+
                      "p.descripcion"+
                      " FROM tb_programapyp p "+condicion;

        System.out.println("Sql BUSCAR tb_programapyp edad, sexo: "+sql);

        PreparedStatement stmt = conn.prepareStatement(sql);
        
        stmt.setInt(1, intEdad); 
        stmt.setString(2, String.valueOf(chrMedEdad)); 
        stmt.setInt(3, idsexo);
        
        try {
            ResultSet res = stmt.executeQuery();
            try {
                while (res.next()) {
                    ProgramapypVO ProgramapypVO = new ProgramapypVO();
                    ProgramapypVO.setId(res.getInt("id"));
                    ProgramapypVO.setCodigo(res.getString("codigo"));
                    ProgramapypVO.setDescripcion(res.getString("descripcion"));
                    listProgramapypVO.add(ProgramapypVO);
                }
            } finally {
                res.close();
            }
        } finally {
            stmt.close();
            if (!conn.isClosed())
               conn.close();
        }

        return listProgramapypVO;
    }
    

  


}